﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Waker.Entitys;

namespace Waker.DAL
{
    public class DAL_Records : DAL_Base
    {
        #region 工资确认
        public DataTable WagesSearch(int year, int month, string depID, string name, string no, string idCard)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT * FROM  T_Wages AS A
LEFT JOIN tbEmployee AS B
ON A.Emp_ID=B.Emp_ID
LEFT JOIN tbDepartment AS C
ON B.Dept_ID=c.Dept_ID
WHERE A.WagesYear=@WagesYear
AND A.WagesMonth=@WagesMonth 
");
            listParams.Add(new En_Param("WagesYear", DbType.Int32, year));
            listParams.Add(new En_Param("WagesMonth", DbType.Int32, month));
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(B.Dept_ID+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND B.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND B.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND b.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        #endregion

        #region 事件
        public bool InsertEventRecord(List<En_ADSEventData> list)
        {
            if (list.Count < 1)
                return false;
            var sql = @"
INSERT INTO tbEvent_Record
        ( Event_ID ,
          Event_Time ,
          Event_Name ,
          Event_Device ,
          Event_SubDevice ,
          Event_Card ,
          Event_EmpID ,
          Event_EmpName ,
          Event_EmpNo ,
          Event_EmpDept ,
          Event_EventValue ,
          Event_AttachValue ,
          Event_IsOpenDoor ,
          Event_IsBrushCard ,
          Event_DeviceID ,
          Event_LSubDeviceID ,
          Event_InsertTime ,
          ViollateCard_Reasion ,
          Is_AlarmEvent ,
          AlarmEvent_AlarmClassID ,
          Reserver_Str ,
          Reserver_Int
        )
VALUES  ( @Event_ID ,
          @Event_Time ,
          @Event_Name ,
          @Event_Device ,
          @Event_SubDevice ,
          @Event_Card ,
          @Event_EmpID ,
          @Event_EmpName ,
          @Event_EmpNo ,
          @Event_EmpDept ,
          @Event_EventValue ,
          @Event_AttachValue ,
          @Event_IsOpenDoor ,
          @Event_IsBrushCard ,
          @Event_DeviceID ,
          @Event_LSubDeviceID ,
          @Event_InsertTime ,
          @ViollateCard_Reasion ,
          @Is_AlarmEvent ,
          @AlarmEvent_AlarmClassID ,
          @Reserver_Str ,
          @Reserver_Int
        )
";
            foreach (var en in list)
            {
                var listParams = new List<En_Param>();
                listParams.Add(new En_Param("Event_ID", DbType.String, en.Event_ID));

                listParams.Add(new En_Param("Event_Time", DbType.DateTime, en.Event_Time));
                listParams.Add(new En_Param("Event_Name", DbType.String, en.Event_Name));
                listParams.Add(new En_Param("Event_Device", DbType.String, en.Event_Device));
                listParams.Add(new En_Param("Event_SubDevice", DbType.String, en.Event_SubDevice));
                listParams.Add(new En_Param("Event_Card", DbType.String, en.Event_Card));
                listParams.Add(new En_Param("Event_EmpID", DbType.String, en.Emp_ID));
                listParams.Add(new En_Param("Event_EmpName", DbType.String, en.EmplyeeName));
                listParams.Add(new En_Param("Event_EmpNo", DbType.String, en.Emp_NO));
                listParams.Add(new En_Param("Event_EmpDept", DbType.String, en.Dept_ID));
                listParams.Add(new En_Param("Event_EventValue", DbType.Int32, en.Event_EventValue));

                listParams.Add(new En_Param("Event_AttachValue", DbType.Int32, en.Event_AttachValue));
                listParams.Add(new En_Param("Event_IsOpenDoor", DbType.Int32, en.Event_IsOpenDoor));
                listParams.Add(new En_Param("Event_IsBrushCard", DbType.Int32, en.Event_IsBrushCard));
                listParams.Add(new En_Param("Event_DeviceID", DbType.String, en.Event_DeviceID));
                listParams.Add(new En_Param("Event_LSubDeviceID", DbType.String, en.Event_LSubDeviceID));
                listParams.Add(new En_Param("Event_InsertTime", DbType.DateTime, DateTime.Now));
                listParams.Add(new En_Param("ViollateCard_Reasion", DbType.String, en.ViollateCard_Reasion));


                listParams.Add(new En_Param("Is_AlarmEvent", DbType.Int32, en.Is_AlarmEvent));
                listParams.Add(new En_Param("AlarmEvent_AlarmClassID", DbType.String, en.AlarmEvent_AlarmClassID));
                listParams.Add(new En_Param("Reserver_Str", DbType.String, string.Empty));
                listParams.Add(new En_Param("Reserver_Int", DbType.Int32, 0));

                base.ExecuteNonQuerySql(sql, listParams);
            }
            return true;
        }
        public bool UpdateEventRecordPic(string eventId, string picPaht)
        {
            return base.ExecuteNonQuerySql("UPDATE tbEvent_Record SET Event_Pic=@Event_Pic WHERE Event_ID=@Event_ID",
                new En_Param("Event_ID", DbType.String, eventId),
                new En_Param("Event_Pic", DbType.String, picPaht)
                ) >= 0;
        }
        #endregion

        #region 未刷卡记录
        public DataTable EmployeeNoWipeSeach(DateTime BeginDate, DateTime endDate, string depID, string name, string no, string idCard)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT 
IsSelect=CONVERT(BIT,0)
,IsGrantCard=(CASE WHEN ISNULL(B.Emp_Card_ID,'')<>'' THEN 1 ELSE 0 END)
,Sex=(CASE WHEN ISNULL(A.Emp_Sex,0)=1 THEN '男' ELSE '女' END) 
,A.Emp_ID ,
A.Dept_ID ,
A.Emp_No ,
A.Emp_Name ,
A.Emp_Sex ,
A.Emp_Address ,
A.Emp_Phone ,
A.Emp_IDCard ,
A.Emp_birthdate ,
--A.Emp_Photo ,
A.Emp_InternalID ,
A.Emp_jobID ,
A.Emp_Native ,
A.Emp_nationId ,
A.Emp_eduId ,
A.Emp_Email ,
A.Emp_State ,
A.Emp_IsMaintain ,
A.Emp_IsLeava ,
A.Reserver_Str ,
A.Reserver_Int,
A.IsFingerAny,
A.FingerId,
A.IsUploadFinger,
c.Dept_Name,
Job=d.DataName,
Nation=e.Nation_Name,
f.Edu_Name,
WorkTypeName=H.DataName,
LastWipeDate=(SELECT TOP 1 Event_Time FROM tbEvent_Record WHERE Event_Card=B.Card_No ORDER BY Event_Time DESC)
FROM tbEmployee AS A 
LEFT JOIN tbEmp_Card AS B ON a.Emp_ID=b.Emp_ID
LEFT JOIN tbDepartment AS C ON A.Dept_ID=C.Dept_ID
LEFT JOIN T_BaseData AS D ON A.Emp_jobID=D.ID AND D.DataType='职位'
LEFT JOIN tbEmp_Nation AS E ON A.Emp_nationId=E.Nation_ID 
LEFT JOIN tbEmp_Education AS F ON A.Emp_eduId=F.Edu_ID
LEFT JOIN T_BaseData AS H ON A.WorkType=H.ID AND H.DataType='工种'
WHERE ISNULL(Emp_IsLeava,0)=0
AND  NOT  EXISTS (SELECT * FROM tbEvent_Record WHERE Event_Card=B.Card_No AND Event_Time BETWEEN  @BeginDate AND @endDate)
");

            listParams.Add(new En_Param("BeginDate", DbType.DateTime, BeginDate));
            listParams.Add(new En_Param("endDate", DbType.DateTime, endDate));
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND A.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND A.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND A.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(A.Dept_ID+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        #endregion

        #region 教育培训记录
        public DataTable EmployeeNoEduSearch(DateTime BeginDate, DateTime endDate, string depID, string name, string no, string idCard, string safeID)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT 
A.Event_Time , 
A.Event_Card ,
A.Event_Pic,
C.Emp_No ,
C.Emp_Name ,
C.Emp_IDCard ,
D.Dept_Name,
Job=E.DataName, 
WorkTypeName=H.DataName
FROM tbEvent_Record AS A
LEFT JOIN T_SafeTimePeriod AS B ON A.Event_DeviceID=B.Device_ID AND A.Event_LSubDeviceID=B.LSubDevice_ID
LEFT JOIN tbEmployee AS C ON A.Event_EmpID=C.Emp_ID
LEFT JOIN tbDepartment AS D ON C.Dept_ID=D.Dept_ID
LEFT JOIN T_BaseData AS E ON C.Emp_jobID=E.ID AND E.DataType='职位' 
LEFT JOIN T_BaseData AS H ON C.WorkType=H.ID AND H.DataType='工种'
WHERE ISNULL(A.Event_IsOpenDoor,0)=1 AND ISNULL(A.Event_IsBrushCard,0)=1
AND B.Safe_ID IS NOT NULL AND C.Emp_ID IS NOT NULL
AND A.Event_Time BETWEEN B.BeginDate AND B.EndDate
AND  CHARINDEX(CONVERT(VARCHAR(2), Datepart(weekday, A.Event_Time)-1)+',',B.SafeWeek+',')>0 
AND  CONVERT(char(12),A.Event_Time,108) BETWEEN CONVERT(char(12),B.BeginTime,108) AND CONVERT(char(12),B.EndTime,108)
AND A.Event_Time BETWEEN  @BeginDate AND @endDate
");

            listParams.Add(new En_Param("BeginDate", DbType.DateTime, BeginDate));
            listParams.Add(new En_Param("endDate", DbType.DateTime, endDate));
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND C.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND C.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND C.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(C.Dept_ID+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            if (!string.IsNullOrEmpty(safeID))
            {
                sb.Append(" AND CHARINDEX(Convert(varchar(20),B.Safe_ID)+',',@Safe_ID+',')>0");
                listParams.Add(new En_Param("Safe_ID", DbType.String, safeID));
            }
            sb.Append(" ORDER BY A.Event_Time DESC");
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        #endregion

        #region 刷卡记录
        public DataTable WipeRecordSeach(DateTime BeginDate, DateTime endDate, string depID, string name, string no, string idCard, bool Is_AlarmEvent)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT 
A.*,
C.Dept_Name,
B.Emp_Name
FROM tbEvent_Record AS A 
LEFT JOIN tbEmployee AS B
ON A.Event_EmpID=B.Emp_ID
LEFT JOIN tbDepartment AS C
ON A.Event_EmpDept=c.Dept_ID
WHERE b.Emp_ID IS NOT NULL AND C.Dept_ID IS NOT NULL
AND ISNULL(A.Is_AlarmEvent,0)=@Is_AlarmEvent
AND A.Event_Time BETWEEN @BeginDate AND @endDate
");

            listParams.Add(new En_Param("BeginDate", DbType.DateTime, BeginDate));
            listParams.Add(new En_Param("endDate", DbType.DateTime, endDate));
            listParams.Add(new En_Param("Is_AlarmEvent", DbType.Boolean, Is_AlarmEvent));
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND B.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND B.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND B.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(A.Event_EmpDept+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        #endregion
    }
}
